using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
    public class OrderItems : IDisposable
    {
        private SqlDataAdapter dataAdapter;

        private SqlCommand selectCommand;

        private SqlCommand insertCommand;

        private SqlCommand updateCommand;

        private SqlCommand deleteCommand;

        public bool UpdateProductType(string sOrderCode, int nProductTypeID, decimal mPrice)
        {
            string cmdText = "update Product SET Product.ProductTypeID=@ProductTypeID,Product.Price=@Price from Product inner join [Order] ON Product.OrderID=[Order].PKID where [Order].Code=@OrderCode";
            try
            {
                this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money));
                this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
                this.updateCommand.Parameters["@ProductTypeID"].Value = nProductTypeID;
                this.updateCommand.Parameters["@Price"].Value = mPrice;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductType(string[] aProduct, int nProductTypeID, decimal mPrice)
        {
            string text = "update Product SET Product.ProductTypeID=@ProductTypeID,Product.Price=@Price from Product";
            string text2 = "";
            for (int i = 0; i < aProduct.Length; i++)
            {
                if (text2 == "")
                {
                    text2 = " where PKID in ('" + aProduct[i] + "'";
                }
                else
                {
                    text2 = text2 + ",'" + aProduct[i] + "'";
                }
            }
            text2 += ")";
            text += text2;
            try
            {
                this.updateCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money));
                this.updateCommand.Parameters["@ProductTypeID"].Value = nProductTypeID;
                this.updateCommand.Parameters["@Price"].Value = mPrice;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductCode(string[] aProduct, string sProductCode)
        {
            string str = "update Product SET Product.ProductCode=@ProductCode from Product";
            string text = "";
            for (int i = 0; i < aProduct.Length; i++)
            {
                if (text == "")
                {
                    text = " where PKID in ('" + aProduct[i] + "'";
                }
                else
                {
                    text = text + ",'" + aProduct[i] + "'";
                }
            }
            text += ")";
            try
            {
                this.updateCommand = new SqlCommand(str + text, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductCode", SqlDbType.VarChar, 32));
                this.updateCommand.Parameters["@ProductCode"].Value = sProductCode;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductQuantity(string[] aProduct, int nQuantity, char cFlag)
        {
            if (cFlag != '+' && cFlag != '-' && cFlag != '*' && cFlag != '/')
            {
                throw new Exception("给出的第三个参数应为 '+' '-' '*' '/'");
            }
            string str = "update Product SET Product.Quantity=Product.Quantity" + cFlag + "@Quantity from Product";
            string text = "";
            for (int i = 0; i < aProduct.Length; i++)
            {
                if (text == "")
                {
                    text = " where PKID in ('" + aProduct[i] + "'";
                }
                else
                {
                    text = text + ",'" + aProduct[i] + "'";
                }
            }
            text += ")";
            try
            {
                this.updateCommand = new SqlCommand(str + text, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                this.updateCommand.Parameters["@Quantity"].Value = nQuantity;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductRemark(string sOrderCode, string sRemark)
        {
            string cmdText = "update Product SET Product.Remark=@Remark from Product inner join [Order] ON Product.OrderID=[Order].PKID where [Order].Code=@OrderCode";
            try
            {
                this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
                this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
                this.updateCommand.Parameters["@Remark"].Value = sRemark;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductRemark(string[] aProduct, string sRemark)
        {
            string text = "update Product SET Product.Remark=@Remark from Product";
            string text2 = "";
            for (int i = 0; i < aProduct.Length; i++)
            {
                if (text2 == "")
                {
                    text2 = " where PKID in ('" + aProduct[i] + "'";
                }
                else
                {
                    text2 = text2 + ",'" + aProduct[i] + "'";
                }
            }
            text2 += ")";
            text += text2;
            try
            {
                this.updateCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
                this.updateCommand.Parameters["@Remark"].Value = sRemark;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateProductTechnics(string sProductID, string sUpdateWay, int nOperatorID, string sTechnicsXMLDocument)
        {
            try
            {
                this.updateCommand = new SqlCommand("UpdateProductTechnics", new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.CommandType = CommandType.StoredProcedure;
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@UpdateWay", SqlDbType.VarChar, 8));
                this.updateCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@technicsXMLDocument", SqlDbType.NVarChar, 1000));
                this.updateCommand.Parameters["@ProductID"].Value = sProductID;
                this.updateCommand.Parameters["@UpdateWay"].Value = sUpdateWay;
                this.updateCommand.Parameters["@OperatorID"].Value = nOperatorID;
                this.updateCommand.Parameters["@technicsXMLDocument"].Value = sTechnicsXMLDocument;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public DataTable GetOrderItems(long nOrderID, Sequence eSequence)
        {
            DataTable dataTable = new DataTable("OrderItem");
            string text = "SELECT p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.SpecialShapedName as 加工要求,p.Quantity AS 数量,dbo.ItemSquare(p.Width,p.Height,p.Quantity) AS 面积,CAST(p.Price as decimal(9, 2)) AS 价格,p.Remark AS 备注,dbo.GetDetailedTechnicsOfProductID(p.PKID) as 工艺 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE p.OrderID=@OrderID";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    //text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    text += " ORDER BY pt.[Sequence], p.PKID";
                    break;
                case Sequence.BySize:
                    text += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@OrderID"].Value = nOrderID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataSet GetOrderItemsForOrder(long nOrderID, Sequence eSequence)
        {
            DataSet dataSet = new DataSet("OrderItem");
            string str = "SELECT p.PKID AS 产品ID,pt.[Name] AS 产品类型,pt.Unit AS 类型单位,p.ProductCode AS 产品编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.SpecialShapedName as 加工要求,p.Quantity AS 产品数量,CAST(p.Price as decimal(9, 2)) AS 产品价格,p.Remark AS 产品备注,dbo.GetDetailedTechnicsOfProductID(p.PKID) as 产品工艺,dbo.ProductTechnicsValue(p.PKID,p.Quantity) as 工艺金额 FROM Product AS p INNER JOIN ProductType AS pt ON pt.PKID=p.ProductTypeID WHERE p.OrderID=@OrderID";
            string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*p.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID WHERE p.OrderID=@OrderID group by ts.technicsName,ts.unitPrice order by ts.technicsName";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    //str += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    str += " ORDER BY pt.[Sequence], p.PKID";
                    break;
                case Sequence.BySize:
                    str += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@OrderID"].Value = nOrderID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataSet);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataSet;
        }

        public DataTable GetDataForLabel(long nOrderID, Sequence eSequence)
        {
            DataTable dataTable = new DataTable();
            string text = "SELECT o.CustomerNameOnPrintedLabel as 标签上的客户名称,c.CompanyName as 公司名称,pj.ProjectName as 计划名称,o.Remark as 订单备注,p.PKID as 产品ID,pt.[Name] as 产品类型,pt.Unit as 类型单位,p.ProductCode as 产品编号,p.Width as 宽,p.SubtenseOfWidth as '宽(短边)',p.Height as 高,p.SubtenseOfHeight as '高(短边)',p.SpecialShapedName as 加工要求,p.Quantity as 产品数量,CAST(p.Price as decimal(9, 2)) AS 产品价格,p.Remark as 产品备注,dbo.GetTechnicsOfProductID(p.PKID) as 产品工艺,pt.LabelMultiple as 标签倍数 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID INNER JOIN [Order] AS o ON p.OrderID=o.PKID INNER JOIN [Project] AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID WHERE o.PKID=@OrderID";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    //text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    text += " ORDER BY pt.[Sequence], p.PKID";
                    break;
                case Sequence.BySize:
                    text += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@OrderID"].Value = nOrderID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetDataForLabel(string[] arrOrderCodes, Sequence eSequence)
        {
            if (arrOrderCodes == null || arrOrderCodes.Length < 1)
            {
                throw new Exception("请给出一个或多个订单号，才能获取标签打印的数据！");
            }
            DataTable dataTable = new DataTable();
            string text = "SELECT o.LabelName as 标签名称,c.CompanyName as 公司名称,pj.ProjectName as 计划名称,p.PKID as 产品ID,pt.[Name] as 产品类型,pt.Unit as 类型单位,p.ProductCode as 产品编号,p.Width as 宽,p.SubtenseOfWidth as '宽(短边)',p.Height as 高,p.SubtenseOfHeight as '高(短边)',p.SpecialShapedName as 加工要求,p.Quantity as 产品数量,CAST(p.Price as decimal(9, 2)) AS 产品价格,p.Remark as 产品备注,dbo.GetTechnicsOfProductID(p.PKID) as 产品工艺,pt.LabelMultiple as 标签倍数 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID INNER JOIN [Order] AS o ON p.OrderID=o.PKID INNER JOIN [Project] AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID";
            string text2 = "";
            for (int i = 0; i < arrOrderCodes.Length; i++)
            {
                if (text2 == "")
                {
                    text2 = " o.Code=" + arrOrderCodes[i];
                }
                else
                {
                    text2 = text2 + " or o.Code=" + arrOrderCodes[i];
                }
                if (text2 != "")
                {
                    text2 = " WHERE " + text2;
                }
            }
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    break;
                case Sequence.BySize:
                    text += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataSet LoadOrderItem(string sProductID)
        {
            DataSet dataSet = new DataSet("Product");
            string str = "SELECT p.PKID AS 产品ID,pt.PKID as 产品类型ID,pt.[Name] AS 产品类型,pt.Unit as 类型单位,p.ProductCode AS 产品编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.SpecialShapedName as 加工要求,p.Quantity AS 产品数量,CAST(p.Price as decimal(9, 2)) AS 价格,p.Remark AS 产品备注 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE p.PKID=@ProductID";
            string str2 = " select technicsName as 名称,quantity as 数量,unitPrice as 单价,Remark as 备注 from Technics where ProductID=@ProductID order by SequenceCode";
            try
            {
                this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                this.selectCommand.Parameters["@ProductID"].Value = sProductID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataSet);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataSet;
        }

        public DataTable GetOrderItemsByCondition(OrderItemCondition oic)
        {
            string text = "";
            if (oic.CustomerID > -1)
            {
                text = text + "c.PKID=" + oic.CustomerID.ToString();
            }
            if (oic.ProjectID > -1)
            {
                text = text + "pj.PKID=" + oic.ProjectID.ToString();
            }
            if (oic.OrderCode != null)
            {
                text = text + "o.Code LIKE '%" + oic.OrderCode + "%'";
            }
            if (oic.ProductTypeID > -1)
            {
                if (text == "")
                {
                    text = "pt.PKID=" + oic.ProductTypeID.ToString();
                }
                else
                {
                    text = text + " AND pt.PKID=" + oic.ProductTypeID.ToString();
                }
            }
            if (oic.ProductCode != null)
            {
                if (text == "")
                {
                    text = "ProductCode LIKE '%" + oic.ProductCode + "%'";
                }
                else
                {
                    text = text + " AND ProductCode LIKE '%" + oic.ProductCode + "%'";
                }
            }
            if (oic.Width > 0.0)
            {
                if (oic.WidthUpWarp > 0 || oic.WidthDownWarp > 0)
                {
                    if (text != "")
                    {
                        text += " AND";
                    }
                    text = text + " Width<=" + ((oic.WidthUpWarp == 0) ? oic.Width.ToString() : (oic.Width + (double)oic.WidthUpWarp).ToString());
                    text = text + " AND Width>=" + ((oic.WidthDownWarp == 0) ? oic.Width.ToString() : (oic.Width - (double)oic.WidthDownWarp).ToString());
                }
                else if (text == "")
                {
                    text = text + "Width=" + oic.Width.ToString();
                }
                else
                {
                    text = text + " AND Width=" + oic.Width.ToString();
                }
            }
            if (oic.Height > 0.0)
            {
                if (oic.HeightUpWarp > 0 || oic.HeightDownWarp > 0)
                {
                    if (text != "")
                    {
                        text += " AND";
                    }
                    text = text + " Height<=" + ((oic.HeightUpWarp == 0) ? oic.Height.ToString() : (oic.Height + (double)oic.HeightUpWarp).ToString());
                    text = text + " AND Height>=" + ((oic.HeightDownWarp == 0) ? oic.Height.ToString() : (oic.Height - (double)oic.HeightDownWarp).ToString());
                }
                else if (text == "")
                {
                    text = text + "Height=" + oic.Height.ToString();
                }
                else
                {
                    text = text + " AND Height=" + oic.Height.ToString();
                }
            }
            if (oic.Remark != null)
            {
                if (text == "")
                {
                    text = text + "p.Remark LIKE '%" + oic.Remark + "%'";
                }
                else
                {
                    text = text + " AND p.Remark LIKE '%" + oic.Remark + "%'";
                }
            }
            if (oic.SpeakForDateStart != null)
            {
                if (text == "")
                {
                    text += "o.SpeakForDate>=CONVERT(DATETIME,'@SpeakForDateStart') AND o.SpeakForDate<=CONVERT(DATETIME,'@SpeakForDateEnd')".Replace("@SpeakForDateStart", oic.SpeakForDateStart).Replace("@SpeakForDateEnd", oic.SpeakForDateEnd);
                }
                else
                {
                    text += " AND o.SpeakForDate>=CONVERT(DATETIME,'@SpeakForDateStart') AND o.SpeakForDate<=CONVERT(DATETIME,'@SpeakForDateEnd')".Replace("@SpeakForDateStart", oic.SpeakForDateStart).Replace("@SpeakForDateEnd", oic.SpeakForDateEnd);
                }
            }
            if (text != "")
            {
                text = " WHERE " + text;
            }
            string text2 = "SELECT o.CustomerNameOnPrintedLabel as 标签上的客户名称, c.CompanyName AS 公司名称,pj.ProjectName AS 计划名称,o.Remark as 订单备注,o.Code AS 订单号,p.PKID AS '产品ID',pt.[Name] AS 产品类型,pt.Unit as 类型单位,p.ProductCode AS 产品编号,p.Width AS 宽,p.Height AS 高,p.SpecialShapedName as 加工要求,p.Quantity AS 产品数量,CAST(p.Price as decimal(9, 2)) AS 产品价格,p.Remark AS 产品备注,dbo.GetTechnicsOfProductID(p.PKID) as 产品工艺,pt.LabelMultiple as 标签倍数 FROM Customer AS c INNER JOIN Project AS pj ON c.PKID=pj.CustomerID INNER JOIN [Order] AS o ON pj.PKID=o.ProjectID INNER JOIN Product AS p ON o.PKID=p.OrderID INNER JOIN ProductType AS pt ON pt.PKID=p.ProductTypeID";
            text2 = text2 + text + " Order by pt.Sequence,c.CompanyName";
            DataTable dataTable = new DataTable("Product");
            try
            {
                this.selectCommand = new SqlCommand(text2, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetProductIDCollection(string sOrderCode)
        {
            DataTable dataTable = new DataTable("Shipping");
            string cmdText = "select p.PKID as 产品ID, p.Quantity as 产品数量, dbo.InvoiceItemQuantityOfOrderItem(p.PKID) as 发货数量 from Product as p inner join [Order] as o on p.OrderID=o.PKID where o.Code=@OrderCode";
            try
            {
                this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
                this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetTechnics_不用(string strProductID)
        {
            DataTable dataTable = new DataTable("Technics");
            string cmdText = "select technicsName as 名称,quantity as 数量,unitPrice as 单价,Remark as 备注 from Technics where ProductID=@productID order by SequenceCode";
            try
            {
                this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@productID", SqlDbType.VarChar, 32));
                this.selectCommand.Parameters["@productID"].Value = strProductID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetOrderItemsForInvoice(long nOrderID)
        {
            DataTable dataTable = new DataTable("OrderItem");
            string text = "SELECT p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号, p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.Quantity AS 数量,dbo.InvoiceItemQuantityOfOrderItem(p.PKID) as 发货数量, p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE p.OrderID=@OrderID";
            text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@OrderID"].Value = nOrderID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetOrderItemsForTempered(long nOrderID)
        {
            DataTable dataTable = new DataTable("OrderItem");
            string text = "SELECT p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号, p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',p.Quantity AS 数量,dbo.TemperedQuantityOfOrderItem(p.PKID) as 已完成, p.Remark AS 备注,dbo.GetTechnicsOfProductID(p.PKID) as 工艺 FROM Product AS p INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE p.OrderID=@OrderID";
            text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@OrderID"].Value = nOrderID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public bool SetOrderItemsForTempered(string sProductID, int nQuantity)
        {
            DataTable dataTable = new DataTable("OrderItem");
            string cmdText;
            if (nQuantity <= 0)
            {
                cmdText = "delete Tempered where ProductID=@productID";
            }
            else
            {
                cmdText = "declare @RowCount int select @RowCount = count(*) from Tempered where ProductID=@productID if @RowCount = 0 begin   insert Tempered values(@ProductID, @Quantity, getdate()) end else begin   update Tempered set Quantity = @Quantity where ProductID = @ProductID end";
            }
            try
            {
                this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                this.selectCommand.Parameters["@ProductID"].Value = sProductID;
                this.selectCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                this.selectCommand.Parameters["@Quantity"].Value = nQuantity;
                int num = this.selectCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public string InsertOrderItem(long nOrderID, int nProductTypeID, string sProductCode, decimal mWidth, string sSubtenseOfWidth, decimal mHeight, string sSubtenseOfHeight, string sSpecialShapedName, int nQuantity, decimal mPrice, string sRemark, int nOperatorID, string sTechnicsXMLDocument)
        {
            string result = null;
            try
            {
                if (this.insertCommand == null)
                {
                    this.insertCommand = new SqlCommand("InsertOrderItem", new SqlConnection(AppSettings.ConnectionString));
                    this.insertCommand.CommandType = CommandType.StoredProcedure;
                    this.insertCommand.Connection.Open();
                    this.insertCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                    this.insertCommand.Parameters["@ProductID"].Direction = ParameterDirection.InputOutput;
                    this.insertCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
                    this.insertCommand.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Int));
                    this.insertCommand.Parameters.Add(new SqlParameter("@ProductCode", SqlDbType.NVarChar, 32));
                    this.insertCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Float));
                    this.insertCommand.Parameters.Add(new SqlParameter("@SubtenseOfWidth", SqlDbType.VarChar, 16));
                    this.insertCommand.Parameters.Add(new SqlParameter("@Height", SqlDbType.Float));
                    this.insertCommand.Parameters.Add(new SqlParameter("@SubtenseOfHeight", SqlDbType.VarChar, 16));
                    this.insertCommand.Parameters.Add(new SqlParameter("@SpecialShapedName", SqlDbType.NVarChar, 32));
                    this.insertCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                    this.insertCommand.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money));
                    this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
                    this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
                    this.insertCommand.Parameters.Add(new SqlParameter("@technicsXMLDocument", SqlDbType.NVarChar, 1000));
                }
                this.insertCommand.Parameters["@ProductID"].Value = "";
                this.insertCommand.Parameters["@OrderID"].Value = nOrderID;
                this.insertCommand.Parameters["@ProductTypeID"].Value = nProductTypeID;
                this.insertCommand.Parameters["@ProductCode"].Value = sProductCode;
                this.insertCommand.Parameters["@Width"].Value = mWidth;
                this.insertCommand.Parameters["@SubtenseOfWidth"].Value = sSubtenseOfWidth;
                this.insertCommand.Parameters["@Height"].Value = mHeight;
                this.insertCommand.Parameters["@SubtenseOfHeight"].Value = sSubtenseOfHeight;
                this.insertCommand.Parameters["@SpecialShapedName"].Value = sSpecialShapedName;
                this.insertCommand.Parameters["@Quantity"].Value = nQuantity;
                this.insertCommand.Parameters["@Price"].Value = mPrice;
                this.insertCommand.Parameters["@Remark"].Value = sRemark;
                this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
                this.insertCommand.Parameters["@technicsXMLDocument"].Value = sTechnicsXMLDocument;
                this.insertCommand.ExecuteNonQuery();
                result = this.insertCommand.Parameters["@ProductID"].Value.ToString();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }


        public void InsertTechnics(string productID,string SequenceCode,string technicsName, string quantity,string unitPrice,string Remark)
        {
            string sqlText = "insert into Technics values(@ProductID, @SequenceCode,@technicsName,@quantity,@unitPrice,@Remark)";
            try
            {
                this.insertCommand = new SqlCommand(sqlText, new SqlConnection(AppSettings.ConnectionString));
                this.insertCommand.Connection.Open();
                this.insertCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                this.insertCommand.Parameters["@ProductID"].Value = productID;
                this.insertCommand.Parameters.Add(new SqlParameter("@SequenceCode", SqlDbType.Int));
                this.insertCommand.Parameters["@SequenceCode"].Value = SequenceCode;
                this.insertCommand.Parameters.Add(new SqlParameter("@technicsName", SqlDbType.VarChar, 32));
                this.insertCommand.Parameters["@technicsName"].Value = technicsName;
                this.insertCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Float));
                this.insertCommand.Parameters["@quantity"].Value = quantity;
                this.insertCommand.Parameters.Add(new SqlParameter("@unitPrice", SqlDbType.Money));
                this.insertCommand.Parameters["@unitPrice"].Value = unitPrice;
                this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.VarChar, 32));
                this.insertCommand.Parameters["@Remark"].Value = Remark;
                this.insertCommand.ExecuteNonQuery();


            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public bool UpdateOrderItem(string sProductID, int nProductTypeID, string sProductCode, decimal mWidth, string sSubtenseOfWidth, decimal mHeight, string sSubtenseOfHeight, string sSpecialShapedName, int nQuantity, decimal mPrice, string sRemark, int nOperatorID, string sTechnicsXMLDocument)
        {
            try
            {
                this.updateCommand = new SqlCommand("UpdateOrderItem", new SqlConnection(AppSettings.ConnectionString));
                this.updateCommand.CommandType = CommandType.StoredProcedure;
                this.updateCommand.Connection.Open();
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@ProductCode", SqlDbType.NVarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Float));
                this.updateCommand.Parameters.Add(new SqlParameter("@SubtenseOfWidth", SqlDbType.VarChar, 16));
                this.updateCommand.Parameters.Add(new SqlParameter("@Height", SqlDbType.Float));
                this.updateCommand.Parameters.Add(new SqlParameter("@SubtenseOfHeight", SqlDbType.VarChar, 16));
                this.updateCommand.Parameters.Add(new SqlParameter("@SpecialShapedName", SqlDbType.NVarChar, 32));
                this.updateCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money));
                this.updateCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 128));
                this.updateCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
                this.updateCommand.Parameters.Add(new SqlParameter("@technicsXMLDocument", SqlDbType.NVarChar, 1000));
                this.updateCommand.Parameters["@ProductID"].Value = sProductID;
                this.updateCommand.Parameters["@ProductTypeID"].Value = nProductTypeID;
                this.updateCommand.Parameters["@ProductCode"].Value = sProductCode;
                this.updateCommand.Parameters["@Width"].Value = mWidth;
                this.updateCommand.Parameters["@SubtenseOfWidth"].Value = sSubtenseOfWidth;
                this.updateCommand.Parameters["@Height"].Value = mHeight;
                this.updateCommand.Parameters["@SubtenseOfHeight"].Value = sSubtenseOfHeight;
                this.updateCommand.Parameters["@SpecialShapedName"].Value = sSpecialShapedName;
                this.updateCommand.Parameters["@Quantity"].Value = nQuantity;
                this.updateCommand.Parameters["@Price"].Value = mPrice;
                this.updateCommand.Parameters["@Remark"].Value = sRemark;
                this.updateCommand.Parameters["@OperatorID"].Value = nOperatorID;
                this.updateCommand.Parameters["@technicsXMLDocument"].Value = sTechnicsXMLDocument;
                this.updateCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public bool DeleteOrderItem(string sOrderItemID, int nOperatorID)
        {
            try
            {
                if (this.deleteCommand == null)
                {
                    this.deleteCommand = new SqlCommand("DeleteOrderItem", new SqlConnection(AppSettings.ConnectionString));
                    this.deleteCommand.CommandType = CommandType.StoredProcedure;
                    this.deleteCommand.Connection.Open();
                    this.deleteCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
                    this.deleteCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
                }
                this.deleteCommand.Parameters["@ProductID"].Value = sOrderItemID;
                this.deleteCommand.Parameters["@OperatorID"].Value = nOperatorID;
                this.deleteCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return true;
        }

        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(true);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this.dataAdapter != null)
                {
                    if (this.dataAdapter.SelectCommand != null)
                    {
                        if (this.dataAdapter.SelectCommand.Connection != null)
                        {
                            this.dataAdapter.SelectCommand.Connection.Dispose();
                            this.dataAdapter.SelectCommand.Connection = null;
                        }
                        this.dataAdapter.SelectCommand.Dispose();
                        this.dataAdapter.SelectCommand = null;
                    }
                    this.dataAdapter.Dispose();
                    this.dataAdapter = null;
                }
                if (this.insertCommand != null)
                {
                    if (this.insertCommand.Connection != null)
                    {
                        this.insertCommand.Connection.Dispose();
                        this.insertCommand.Connection = null;
                    }
                    this.insertCommand.Dispose();
                    this.insertCommand = null;
                }
                if (this.updateCommand != null)
                {
                    if (this.updateCommand.Connection != null)
                    {
                        this.updateCommand.Connection.Dispose();
                        this.updateCommand.Connection = null;
                    }
                    this.updateCommand.Dispose();
                    this.updateCommand = null;
                }
                if (this.deleteCommand != null)
                {
                    if (this.deleteCommand.Connection != null)
                    {
                        this.deleteCommand.Connection.Dispose();
                        this.deleteCommand.Connection = null;
                    }
                    this.deleteCommand.Dispose();
                    this.deleteCommand = null;
                }
            }
        }
    }
}
